package dao;


import util.DBHelper;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ProductTypeDao {

    public long queryTotal(String name,String tid){
        String sql = " select t.* ,s.tname from typedetail t inner join shopping_type s on t.tid = s.tid where 1=1";

        List<String> params = new ArrayList<>();

        if (name !=null && name.trim().isEmpty() ==false){
            sql += " and name like ?";
            params.add("%" + name + "%");
        }

        if (tid !=null && tid.trim().isEmpty() ==false){
            sql += " and t.tid = ?";
            params.add(tid);
        }


        return DBHelper.count(sql,params.toArray());
    }

    // 分页查询
    public List<Map<String,Object>> selectLimit( String name,String tid,int begin, int end){
        String sql = "select t.* ,s.tname from typedetail t inner join shopping_type s on t.tid = s.tid where 1=1";
        List<String> params = new ArrayList<>();

        if (name !=null && name.trim().isEmpty() ==false){
            sql += " and name like ?";
            params.add("%" + name + "%");
        }

        if (tid !=null && tid.trim().isEmpty() ==false){
            sql += " and t.tid = ?";
            params.add(tid);
        }

        sql +=" limit "+begin + "," +end;

        return DBHelper.selectListMap(sql,params.toArray());
    }

    //更新小类的类型
    public int updateMiniType(String name,String typedetailid){
        String sql = "update typedetail set name = ? where typedetailid = ?";
        return DBHelper.update(sql,name,typedetailid);
    }
    //添加小类
    public int InsertMiniType(String name,String tid){
        String sql = "insert into typedetail values(default,?,?)";
        return DBHelper.update(sql,name,tid);
    }


    //删除小类的类型
    public int deleteMiniType(String typedetailid){
        String sql = " delete from typedetail where typedetailid = ? ";
        return DBHelper.update(sql,typedetailid);
    }

    // 商品类型 组合查询
    public List<Map<String, Object>> selectProductType(String tname, String status, int begin, int end){

        String sql = "select * from shopping_type where 1=1 ";

        List<String> params = new ArrayList<>();

        if (tname !=null && tname.trim().isEmpty() ==false){
            sql += " and tname like ?";
            params.add("%"+tname + "%");
        }
        if (status !=null && status.trim().isEmpty() ==false){
            sql += " and status like ?";
            params.add("%"+status + "%");
        }

        sql +=" limit "+begin + "," +end;

        return new DBHelper().selectListMap(sql, params.toArray());
    }

    // 商品类型 查询数量
    public long selectProductTypeCount(String tname , String status){

        String sql = "select * from shopping_type where 1=1 ";

        List<String> params = new ArrayList<>();

        if (tname !=null && tname.trim().isEmpty() ==false){
            sql += " and tname like ?";
            params.add("%"+tname + "%");
        }
        if (status !=null && status.trim().isEmpty() ==false){
            sql += " and status like ?";
            params.add("%"+status + "%");
        }

        return new DBHelper().count(sql,params.toArray());
    }

    // 商品类型 添加
    public int saveProductType(String tname){
        String sql = "insert into shopping_type (tname, tid, status) values (?, default, 1)";
        return new DBHelper().update(sql, tname);
    }

    // 商品类型 修改
    public int modifyProductType(String tname, String tid){
        String sql = "update shopping_type set tname = ? where tid = ?";
        return new DBHelper().update(sql, tname, tid);
    }

    // 商品类型 查重
    public Map<String, Object> Check(String tname){
        String sql = "select count(*) num from shopping_type where tname = ?";
        return new DBHelper().selectOneMap(sql, tname);
    }

    // 商品 逻辑删除和复原
    public int removeStatusProductType(String id, String status){
        String sql = " update shopping_type set status = ? where tid = ? ";
        return DBHelper.update(sql, status, id);
    }

    // 商品 物理删除
    public int removeProductType(String id){
        String sql = " delete from shopping_type where tid = ? ";
        return DBHelper.update(sql, id);
    }
    //查询所有商品类型
    public List<Map<String, Object>> queryType(){
        String sql = "select tname,tid from shopping_type where 1 = 1";
        return DBHelper.selectListMap(sql);
    }
    //根据商品类型查询商品id
    public Map<String,Object> findTid(String tname){
        String sql ="select tid from shopping_type where tname = ?";
        return DBHelper.selectOneMap(sql,tname);
    }
    //查询商品衣服详情类
    public List<Map<String, Object>> findClothingType(String name){
        String sql ="select  p.*,tname,o.quantity from (select sum(quantity) as quantity,proid from orderdetail GROUP BY proid) as o right join  shopping_product p on o.proid = p.proid inner join shopping_type t  on p.tid = t.tid inner join typedetail d on t.tid= d.tid where tname=? GROUP BY proid limit 0,8";
        return DBHelper.selectListMap(sql,name);
    }
    //商品类型分类
    public List<Map<String,Object>> queryimage(String tname,String proname){
        String sql = " select image,o.tname,proname from shopping_type o INNER JOIN shopping_product r ON o.tid=r.tid where 1=1 GROUP BY o.tname ";
        List<String> params = new ArrayList<>();
        if(tname.trim().isEmpty() == false && tname != null){
            sql += " and tname = ?";
            params.add( tname);
        }

        if(proname.trim().isEmpty() == false && proname != null){
            sql += " and proname = ?";
            params.add( proname);
        }
        return DBHelper.selectListMap(sql,params.toArray());
    }

    //查询所有商品的类型
    public List<Map<String, Object>> queryAllShoppingType(){
        String sql = "select tname from shopping_type";
        return DBHelper.selectListMap(sql);
    }
}
